﻿using System;
using System.Collections.Generic;
using System.Data;

namespace SOA.DAL
{
    public class Venda
    {
        public SOA.MODEL.NotaFiscal Salvar(SOA.MODEL.Venda v)
        {
            var nf = GetLastNumeroNF() + 1;
            var sqlInsert = "INSERT INTO TB_VENDA (CPF,VALOR_TOTAL,VALOR_DESCONTO,ID_FILIAL,QTDE_HOT_DOG_1, QTDE_HOT_DOG_2, QTDE_REFRI_1," +
            "QTDE_REFRI_2,PRECO_HOT_DOG_1, PRECO_HOT_DOG_2, PRECO_REFRI_1, PRECO_REFRI_2, DATA,NUMERO_NF) " +
            "VALUES (";
            sqlInsert += "'" + v.cpf + "',";
            sqlInsert += v.valorTotal.ToString("N2") + ",";
            sqlInsert += v.valorDesconto.ToString("N2") + ",";
            sqlInsert += v.idFilial + ",";
            sqlInsert += v.QtdeHotDog1 + ",";
            sqlInsert += v.QtdeHotDog2 + ",";
            sqlInsert += v.QtdeRefri1 + ",";
            sqlInsert += v.QtdeRefri2 + ",";
            sqlInsert += v.PrecoHotDog1.ToString("N2")+ ",";
            sqlInsert += v.PrecoHotDog2.ToString("N2")+ ",";
            sqlInsert += v.PrecoRefri1.ToString("N2") + ",";
            sqlInsert += v.PrecoRefri2.ToString("N2") + ",";
            sqlInsert += "Now(),";
            sqlInsert += nf + ")";
            new Db().Execute(sqlInsert);
            return new SOA.MODEL.NotaFiscal() { numero = nf.ToString() };
        }

        public int GetLastNumeroNF()
        {
            var sql = "SELECT MAX(NUMERO_NF) FROM TB_VENDA";
            var obj = new Db().ExecuteScalar(sql);
            if (obj == null || String.IsNullOrEmpty(obj.ToString()))
                return 0;
            return Convert.ToInt32(obj);
        }

        public SOA.MODEL.VendaDiaria TotalizarVendaDiariaPorFilial(int idFilial, DateTime data)
        {
            var date = data.ToString("MM/dd/yyyy");
            var dt = new Db().Query("SELECT SUM(V.QTDE_HOT_DOG_1) AS QTDE_HOT_DOG_1, "+
                                    "SUM(V.QTDE_HOT_DOG_2) AS QTDE_HOT_DOG_2, "+
                                    "SUM(V.QTDE_REFRI_1) AS QTDE_REFRI_1, "+
                                    "SUM(V.QTDE_REFRI_2) AS QTDE_REFRI_2, "+
                                    "SUM(SWITCH( V.VALOR_DESCONTO > 0 , (V.QTDE_HOT_DOG_1 * V.PRECO_HOT_DOG_1) ,V.VALOR_DESCONTO = 0,( V.QTDE_HOT_DOG_1 * V.PRECO_HOT_DOG_1 * 0.5)) ) AS VALOR_HOT_DOG_1, " +
                                    "SUM(SWITCH( V.VALOR_DESCONTO > 0 , (V.QTDE_HOT_DOG_2 * V.PRECO_HOT_DOG_2) ,V.VALOR_DESCONTO = 0,( V.QTDE_HOT_DOG_2 * V.PRECO_HOT_DOG_2 * 0.5)) ) AS VALOR_HOT_DOG_2, " +
                                    "SUM(SWITCH( V.VALOR_DESCONTO > 0 , (V.QTDE_REFRI_1 * V.PRECO_REFRI_1) ,V.VALOR_DESCONTO = 0,( V.QTDE_REFRI_1 * V.PRECO_REFRI_1 * 0.5)) ) AS VALOR_REFRI_1, " +
                                    "SUM(SWITCH( V.VALOR_DESCONTO > 0 , (V.QTDE_REFRI_2 * V.PRECO_REFRI_2) ,V.VALOR_DESCONTO = 0,( V.QTDE_REFRI_2 * V.PRECO_REFRI_2 * 0.5)) ) AS VALOR_REFRI_2 " +
                                    "FROM TB_VENDA AS V "+
                                    "WHERE V.ID_FILIAL= " + idFilial + " AND V.DATA >= #"+ date +" 00:00:00# AND V.DATA <= #"+ date +" 23:59:59#");
            if (dt.Rows.Count == 0)
                return new MODEL.VendaDiaria();
            var v = new MODEL.VendaDiaria();
            v.QtdeHotDog1 = Convert.ToInt32(dt.Rows[0]["QTDE_HOT_DOG_1"]);
            v.QtdeHotDog2 = Convert.ToInt32(dt.Rows[0]["QTDE_HOT_DOG_2"]);
            v.QtdeRefri1 = Convert.ToInt32(dt.Rows[0]["QTDE_REFRI_1"]);
            v.QtdeRefri2 = Convert.ToInt32(dt.Rows[0]["QTDE_REFRI_2"]);
            v.ValorHotDog1 = Convert.ToDecimal(dt.Rows[0]["VALOR_HOT_DOG_1"]);
            v.ValorHotDog2 = Convert.ToDecimal(dt.Rows[0]["VALOR_HOT_DOG_2"]);
            v.ValorRefri1 = Convert.ToDecimal(dt.Rows[0]["VALOR_REFRI_1"]);
            v.ValorRefri2 = Convert.ToDecimal(dt.Rows[0]["VALOR_REFRI_2"]);
            v.IdFilial = idFilial;
            return v;
        }

        public List<SOA.MODEL.Ranking> GetRankingVendaDiaria(DateTime data)
        {
            
             var date = data.ToString("MM/dd/yyyy");
             var sql = "SELECT * FROM " +
             "( " +
             "SELECT T.ID, T.NOME, T.META, T.VENDA, (T.VENDA / T.META) AS RELACAO FROM ( " +
             "SELECT SUM(V.VALOR_TOTAL) AS VENDA ,  " +
             "F.ID,  " +
             "F.NOME, " +
             "(F.META_HOT_DOG_1 + F.META_HOT_DOG_2 + F.META_REFRI_1 + F.META_REFRI_2) AS META " +
             "FROM TB_FILIAL AS F  " +
             "LEFT JOIN TB_VENDA AS V ON(V.ID_FILIAL  =  F.ID) " +
             "WHERE F.IS_MATRIZ=0 AND (V.DATA IS NULL OR (V.DATA >= #"+ date +" 00:00:00# AND V.DATA <= #"+ date +" 23:59:59#) ) " +
             "GROUP BY F.ID, F.NOME, F.META_HOT_DOG_1, F.META_HOT_DOG_2, F.META_REFRI_1, F.META_REFRI_2 " +
             ") AS T " +
             ") AS A " +
             "ORDER BY A.RELACAO DESC";
            var dt = new Db().Query(sql);
            if (dt.Rows.Count == 0)
                return new List<MODEL.Ranking>();
            var lst = new List<MODEL.Ranking>();
            foreach (DataRow dr in dt.Rows)
            {
                var v = new MODEL.Ranking();
                v.TotalVenda = Convert.ToDecimal(dr["VENDA"]);
                v.Meta = Convert.ToDecimal(dr["META"]);
                v.Relacao = Convert.ToDecimal(dr["RELACAO"]);
                v.Nome = dr["NOME"].ToString();
                v.IdFilial = Convert.ToInt32(dr["ID"]);

                lst.Add(v);
            }
            return lst;
        }
    }
}